package com.hnkywl.sts.dao.cwgl.yjyys;

import com.hnkywl.sts.entity.cwgl.gsjyys.Gsjyys;
import com.hnkywl.sts.entity.cwgl.yjyys.Yjyys;
import net.ruixin.orm.hibernate.Page;
import net.ruixin.orm.hibernate.SimpleHibernateTemplate;
import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.SQLQuery;
import org.hibernate.criterion.CriteriaSpecification;
import org.hibernate.criterion.Projections;
import org.hibernate.criterion.Property;
import org.hibernate.criterion.Restrictions;
import org.hibernate.tool.hbm2x.StringUtils;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.hibernate.type.Type;
import org.springframework.stereotype.Repository;

import java.util.ArrayList;
import java.util.List;
import java.util.Map;

/**
 * Created with IntelliJ IDEA.
 * User: 灵韵
 * Date: 13-9-28
 * Time: 上午10:17
 * To change this template use File | Settings | File Templates.
 */
@Repository
public class YjyysDao extends SimpleHibernateTemplate<Yjyys, Long> {
    public Page<Map> findPage(Long gsid, Long bmid, Integer nf, Integer yf, int pageNo, int pageSize) {
        Page<Map> page = new Page<Map>();
        page.setPageNo(pageNo);
        page.setPageSize(pageSize);
        page.setAutoCount(true);
        page.setOrder("desc");
        page.setOrderBy("dateCreated");
        Criteria criteria = getSession().createCriteria(this.entityClass)
                .createAlias("gs", "gs", CriteriaSpecification.LEFT_JOIN)
                .createAlias("bm", "bm", CriteriaSpecification.LEFT_JOIN)
                .createAlias("createUser", "createUser", CriteriaSpecification.LEFT_JOIN)
                .createAlias("shr", "shr", CriteriaSpecification.LEFT_JOIN)
                .setProjection(Projections.projectionList()
                        .add(Projections.sqlProjection("nf as nf", new String[]{"nf"}, new Type[]{Hibernate.INTEGER}))
                        .add(Projections.sqlProjection("yf as yf", new String[]{"yf"}, new Type[]{Hibernate.INTEGER}))
                        .add(Property.forName("gs.jc").as("gs.jc"))
                        .add(Property.forName("bm.jc").as("bm.jc"))
                        .add(Property.forName("dateCreated").as("dateCreated"))
                        .add(Property.forName("createUser.xm").as("createUser.xm"))
                        .add(Property.forName("shr.xm").as("shr.xm"))
                        .add(Property.forName("zt").as("zt"))
                        .add(Property.forName("id").as("id"))
                        .add(Property.forName("shrq").as("shrq")))
                .setResultTransformer(CriteriaSpecification.ALIAS_TO_ENTITY_MAP);
        if (gsid != null) {
            if (bmid != null)
                criteria.add(Restrictions.and(Restrictions.eq("gs.id", gsid), Restrictions.eq("bm.id", bmid)));
            else
                criteria.add(Restrictions.eq("gs.id", gsid));
        }
        if (nf != null)
            criteria.add(Restrictions.eq("nf", nf));
        if (yf != null)
            criteria.add(Restrictions.eq("yf", yf));


        Page list = this.findByCriteria(page, criteria);
        return list;
    }

    public Boolean existYjyys(Yjyys yjyys){
        StringBuilder condition = new StringBuilder();
        List<Object> param = new ArrayList<Object>();
        condition.append(" gs = ? and nf = ? and yf = ? and deleted != 1");
        param.add(yjyys.getGs());
        param.add(yjyys.getNf());
        param.add(yjyys.getYf());
//        param.add("ybc");
        if(yjyys.getBm()!=null){
            condition.append("and bm = ?");
            param.add(yjyys.getBm());
        }
        String hql = "from Yjyys" + (condition.length() != 0 ? " where " : "") + condition.toString();
        return existsQuery(hql,param.toArray());
    }

    public List<Map> findItems(Boolean isGs,Boolean isBm){
        String where = "";
        List<Object> param = new ArrayList<Object>();
        if(isGs){
            where += " isGs = ?";
            param.add(isGs);
        }
        if(isBm){
            if(where.length()!=0)
                where += " and ";
            where += "isBm = ?";
            param.add(isBm);
        }
        String hql = "select new map(id as id,itemName as ItemName,spacecnt as spacecnt) from YjyysItem"+(where.length()!=0?" where ":"") + where;
        return find(hql,param.toArray());
    }

    public List<Map> findHz(Integer nf,Integer yf){
        String sql = "select id as \"id\",jc as \"jc\"," +
                "       sum(decode(itemname, '经营收入', je)) \"jysr\"," +
                "       sum(decode(itemname, '业务成本', je)) \"ywcb\"," +
                "       sum(decode(itemname, '毛利', je)) \"ml\"," +
                "       sum(decode(itemname, '日均资金占用规模', je)) \"rjzjzygm\"," +
                "       sum(decode(itemname, '额度', je)) \"ed\"," +
                "       sum(decode(itemname, '资金成本', je)) \"zjcb\"," +
                "       sum(decode(itemname, '税费', je)) \"sf\"," +
                "       sum(decode(itemname, '管理成本', je)) \"glcb\"," +
                "       sum(decode(itemname, '人力资源成本', je)) \"rlzycb\"," +
                "       sum(decode(itemname, '利润总额', je)) \"lrze\"," +
                "       sum(decode(itemname, '考核净利润', je)) \"khjlr\"" +
                "  from (select gsjg.id,gsjg.jc, item.itemname, mx.je" +
                "          from T_SYS_GSJG gsjg" +
                "          left join T_BIZ_YJYYS yjyys" +
                "            on yjyys.gs_id = gsjg.id" +
                "           and yjyys.shzt = 'ysh'" +
                "           and yjyys.nf = :nf" +
                "           and yjyys.yf = :yf" +
                "          left join T_BIZ_YJYYSMX mx" +
                "            on yjyys.id = mx.yjyys_id" +
                "          left join T_BIZ_YJYYSITEM item" +
                "            on item.id = mx.item_id" +
                "         where gsjg.type = 'nbgs')" +
                " group by id,jc order by id";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setInteger("nf",nf).setInteger("yf",yf).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }

    public List<Map> findHzSum(Integer nf,Integer yf){
        String sql = "select '合计' \"jc\", sum(decode(itemname, '经营收入', je)) \"jysr\"," +
                "       sum(decode(itemname, '业务成本', je)) \"ywcb\"," +
                "       sum(decode(itemname, '毛利', je)) \"ml\"," +
                "       sum(decode(itemname, '日均资金占用规模', je)) \"rjzjzygm\"," +
                "       sum(decode(itemname, '额度', je)) \"ed\"," +
                "       sum(decode(itemname, '资金成本', je)) \"zjcb\"," +
                "       sum(decode(itemname, '税费', je)) \"sf\"," +
                "       sum(decode(itemname, '管理成本', je)) \"glcb\"," +
                "       sum(decode(itemname, '人力资源成本', je)) \"rlzycb\"," +
                "       sum(decode(itemname, '利润总额', je)) \"lrze\"," +
                "       sum(decode(itemname, '考核净利润', je)) \"khjlr\"" +
                "  from (select item.itemname, mx.je" +
                "          from  T_BIZ_YJYYS yjyys" +
                "          left join T_BIZ_YJYYSMX mx" +
                "            on yjyys.id = mx.yjyys_id" +
                "          left join T_BIZ_YJYYSITEM item" +
                "            on item.id = mx.item_id" +
                "         where yjyys.shzt = 'ysh' and yjyys.nf = :nf and yjyys.yf = :yf)";
        SQLQuery sqlQuery = getSession().createSQLQuery(sql);
        sqlQuery.setInteger("nf",nf).setInteger("yf",yf).setResultTransformer(AliasToEntityMapResultTransformer.INSTANCE);
        return sqlQuery.list();
    }
}
